problem connecting via ODBC when unicode (now in correct forum.).

--001636c5a7bca19def049a5c12a3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

I have have a question that annoys me incredibly.

I have a "Login Roles" that include special chars like "ÆØ=C5" (same in
password) that accesses a view. however this is not possible, as the
connector does not recognize the user.

------------------------------
-----
FATAL: no pg_hba.conf entry for host "127.0.0.1", user "ÆØ=C5", databas=
e
"mydb", SSL off.
-----------------------------------

However if I create another "Login Roles" with the name "abc" (same in
password), I can successfull connect and access the view.

I have almost tried all combinations of client_encoding, have tried to set
configuration string on the connector.

Database is build with: "...... --encoding=3DUNICODE --locale=3DC
--lc-collate=3DC".

If I use pgadmin, everything in the database looks perfect, and the unicode
chars are represented correctly (Login role ÆØ=C5 exists).

Any help much appriciated.

Best Regards
Kim Mortensen

--001636c5a7bca19def049a5c12a3
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

I have have a question that annoys me incredibly.

I=20
have a "Login Roles" that include special chars like "ÆØ=
=C5" (same in=20
password) that accesses a view. however this is not possible, as the=20
connector does not recognize the user.


------------------------------

Re: problem connecting via ODBC when unicode (now in correct forum.).

On Friday 21 January 2011 6:29:07 am Kim Mortensen wrote:
> Hi,
>
> I have have a question that annoys me incredibly.
>
> I have a "Login Roles" that include special chars like "ÆØ=C5" (same =
in
> password) that accesses a view. however this is not possible, as the
> connector does not recognize the user.
>
> ------------------------------
> -----
> FATAL: no pg_hba.conf entry for host "127.0.0.1", user "ÆØ=C5", datab=
ase
> "mydb", SSL off.
> -----------------------------------
>
> However if I create another "Login Roles" with the name "abc" (same in
> password), I can successfull connect and access the view.
>
> I have almost tried all combinations of client_encoding, have tried to set
> configuration string on the connector.
>
> Database is build with: "...... --encoding=3DUNICODE --locale=3DC
> --lc-collate=3DC".
>
> If I use pgadmin, everything in the database looks perfect, and the unico=
de
> chars are represented correctly (Login role ÆØ=C5 exists).
>
> Any help much appriciated.
>
> Best Regards
> Kim Mortensen

To eliminate possibilities.
Can you connect to the database via means other than ODBC using the Role th=
at=20
has the special characters?
What are the lines in your pg_hba file?

--=20
Adrian Klaver
adrian.klaver@gmail.com

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

On Friday 21 January 2011 7:57:14 am Kim Mortensen wrote:
> Adrian,
>
> ok, but one thing I dont undestand is that then it would not work for the
> role "aaa", but it does.
>
> I use the ODBC driver test application that is in the control
> panel/administrative tools/ODBC . in there you can select the driver a,d
> put in the credentials, and press Test. for user "aaa" it works, but for
> "æø=E5" is does not (aaa and æø=E5 are both Login Roles that has =
the exact same
> properties when looking in the database through the pgadmin - And they we=
re
> both inserted the same way using jdbc driver.)
>
> Best Regards
> Kim Mortesnen


CCing the list so more eyes can see it. I was trying to narrow the possible=
=20
error source. Now to ODBC questions.
What version of the driver are you using?
When you installed did you install the Unicode version?






--=20
Adrian Klaver
adrian.klaver@gmail.com

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

--001636c5a7bcdd1f3b049a5d7c47
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

The ODBC driver is the psqlodbc driver version 9 (latest msi installer, jus=
t
downloaded from the homepage a couple of days ago.). This installation
installel both an ANSI and Unicode version, and i have tried to run tests o=
n
both, but not gaining any positive results.

Best Regards
Kim Mortensen


2011/1/21 Adrian Klaver

> On Friday 21 January 2011 7:57:14 am Kim Mortensen wrote:
> > Adrian,
> >
> > ok, but one thing I dont undestand is that then it would not work for t=
he
> > role "aaa", but it does.
> >
> > I use the ODBC driver test application that is in the control
> > panel/administrative tools/ODBC . in there you can select the driver a,=
d
> > put in the credentials, and press Test. for user "aaa" it works, but fo=
r
> > "æø=E5" is does not (aaa and æø=E5 are both Login Roles that ha=
s the exact
> same
> > properties when looking in the database through the pgadmin - And they
> were
> > both inserted the same way using jdbc driver.)
> >
> > Best Regards
> > Kim Mortesnen
>
>
> CCing the list so more eyes can see it. I was trying to narrow the possib=
le
> error source. Now to ODBC questions.
> What version of the driver are you using?
> When you installed did you install the Unicode version?
>
>
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>

--001636c5a7bcdd1f3b049a5d7c47
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

The ODBC driver is the psqlodbc driver version 9 (latest msi=
installer, just downloaded from the homepage a couple of days ago.). This =
installation installel both an ANSI and Unicode version, and i have tried t=
o run tests on both, but not gaining any positive results.


Best Regards
Kim Mortensen

Re: problem connecting via ODBC when unicode (now in correct forum.).

On Friday 21 January 2011 8:10:24 am Kim Mortensen wrote:
> Adrian,
>
> The ODBC driver is the psqlodbc driver version 9 (latest msi installer,
> just downloaded from the homepage a couple of days ago.). This installation
> installel both an ANSI and Unicode version, and i have tried to run tests
> on both, but not gaining any positive results.
>
> Best Regards
> Kim Mortensen
>
>

Hmm, I am going to have to think about this. I will be away from a computer for
a while so I will be able to respond for a bit. In the meantime, when you login
with the 'aaa' role through ODBC can you see the correct characters for the
other role in the database?



--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

--20cf30433fd050c6bb049a5e0584
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

Actually I havent tried to view unicode chars through the login that
succeeds, but that was a good test to see if its the connector that is
messing things up. Unfortunately im now at home, so I dont have access to
the system, so the test will have to be conducted Monday.

By the way, when looking at the create statements shown by pgadmin when I
click on the Login Role, i do see a difference, but not something that woul=
d
cause alarm.

the aaa role has a definition like: create role aaa ....

where the other with unicode have: create role "æø=E5"....

so the unicode is in brackets (cant remenmber if its single or double,), bu=
t
again is just something i notised, and dont think affexts anything, but
worth mentioning..

Best Regards
Kim Mortesnen



2011/1/21 Adrian Klaver

> On Friday 21 January 2011 8:10:24 am Kim Mortensen wrote:
> > Adrian,
> >
> > The ODBC driver is the psqlodbc driver version 9 (latest msi installer,
> > just downloaded from the homepage a couple of days ago.). This
> installation
> > installel both an ANSI and Unicode version, and i have tried to run tes=
ts
> > on both, but not gaining any positive results.
> >
> > Best Regards
> > Kim Mortensen
> >
> >
>
> Hmm, I am going to have to think about this. I will be away from a comput=
er
> for
> a while so I will be able to respond for a bit. In the meantime, when you
> login
> with the 'aaa' role through ODBC can you see the correct characters for t=
he
> other role in the database?
>
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>

--20cf30433fd050c6bb049a5e0584
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

Actually I havent tried to view unicode chars through the lo=
gin that succeeds, but that was a good test to see if its the connector tha=
t is messing things up. Unfortunately im now at home, so I dont have access=
to the system, so the test will have to be conducted Monday.


By the way, when looking at the create statements shown by pgadmin when=
I click on the Login Role, i do see a difference, but not something that w=
ould cause alarm.

the aaa role has a definition like:   create r=
ole aaa ....


where the other with unicode have:=A0 create role "æø=E5"=
.....

so the unicode is in brackets (cant remenmber if its single or =
double,), but again is just something i notised, and dont think affexts any=
thing, but worth mentioning..


Best Regards
Kim Mortesnen

Re: problem connecting via ODBC when unicode (now in correctforum.).

Hi.

I think..
your create role "æø=E5" was saved by pgAdmin by UTF-8 in the databas=
e.
pgAdmin uses the input value of connection directly, however, input=20
value is in coincidence by UTF-8. however, psqlODBC uses the character=20
code of environmental dependence as it is.
as for japanese windows, It is not in agreement with a database by the=20
reason for being SJIS. In the client-server protocol of PostgrSQL,=20
judgment of encoding can't be performed at the time of connection.
therefore, I think that it is difficult to support. however, It may be=20
useful to evasion of a security risk that it uses understanding this=20
specification. but, handling is not recommendation in the reason for=20
being difficult. Probably, only the ASCII character should be used.

I may have misunderstanding.?

Regards,
Hiroshi Saito
(z-saito@ address was lost...)

(2011/01/22 1:48), Kim Mortensen wrote:
> create role "æø=E5"


--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

--20cf3054a5b3d1d7b4049a5f4e47
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Thank you for your input,

it may be that only ascii chars is to be used, however it should be stated
or a bug report filled out, as you can insert Login Roles with different
charakterset as you can actually use.

Best Regards
Kim Mortesnen

2011/1/21 Hiroshi Saito

> Hi.
>
> I think..
> your create role "זרו" was saved by pgAdmin by UTF-8 in th=
e database.
> pgAdmin uses the input value of connection directly, however, input value
> is in coincidence by UTF-8. however, psqlODBC uses the character code of
> environmental dependence as it is.
> as for japanese windows, It is not in agreement with a database by the
> reason for being SJIS. In the client-server protocol of PostgrSQL, judgme=
nt
> of encoding can't be performed at the time of connection.
> therefore, I think that it is difficult to support. however, It may be
> useful to evasion of a security risk that it uses understanding this
> specification. but, handling is not recommendation in the reason for bein=
g
> difficult. Probably, only the ASCII character should be used.
>
> I may have misunderstanding.?
>
> Regards,
> Hiroshi Saito
> (z-saito@ address was lost...)
>
> (2011/01/22 1:48), Kim Mortensen wrote:
>
>> create role "זרו"
>>
>
>

--20cf3054a5b3d1d7b4049a5f4e47
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Thank you for your input,

it may be that only ascii chars is to be u=
sed, however it should be stated or a bug report filled out, as you can ins=
ert Login Roles with different charakterset as you can actually use.


Best Regards
Kim Mortesnen

Re: problem connecting via ODBC when unicode (now in correctforum.).

On 01/21/2011 10:20 AM, Kim Mortensen wrote:
> Thank you for your input,
>
> it may be that only ascii chars is to be used, however it should be
> stated or a bug report filled out, as you can insert Login Roles with
> different charakterset as you can actually use.
>
> Best Regards
> Kim Mortesnen
>
>

I cranked up a virtual instance of Windows XP to try to figure this out.=20
Turns out I need to know more about how encoding works on Windows. I=20
had no problem creating the role æøå on my Linux server an=
d logging in=20
using psql. On Windows not so, I get:

Username [postgres]: æøå
Password for user µ°Õ:
psql: FATAL: password authentication failed for user "æøå=
"

and through ODBC:

2011-01-21 12:16:32 PSTFATAL: password authentication failed for user "=C3=
¦Ã¸Ã=A5"

Seems client encoding is getting in the way somehow, just not sure how.

--=20
Adrian Klaver
adrian.klaver@gmail.com

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

--20cf30433fd099ff7c049a61311e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

Tje letters you are typing are from Danish, but any will do.

Now im working abit from memory, but when the databse is build, initdb is
using UNICODE as encoding, "C" for locale, so it should be independant of
the locale settings.

Best Regards
Kim Mortensen

2011/1/21 Adrian Klaver

> On 01/21/2011 10:20 AM, Kim Mortensen wrote:
>
>> Thank you for your input,
>>
>> it may be that only ascii chars is to be used, however it should be
>> stated or a bug report filled out, as you can insert Login Roles with
>> different charakterset as you can actually use.
>>
>> Best Regards
>> Kim Mortesnen
>>
>>
>>
> I cranked up a virtual instance of Windows XP to try to figure this out.
> Turns out I need to know more about how encoding works on Windows. I had=
no
> problem creating the role æø=E5 on my Linux server and logging in usi=
ng psql.
> On Windows not so, I get:
>
> Username [postgres]: æø=E5
> Password for user µ°=D5:
> psql: FATAL: password authentication failed for user "æø=E5"
>
> and through ODBC:
>
> 2011-01-21 12:16:32 PSTFATAL: password authentication failed for user
> "æø=E5"
>
> Seems client encoding is getting in the way somehow, just not sure how.
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>

--20cf30433fd099ff7c049a61311e
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Adrian,

Tje letters you are typing are from Danish, but any will do.=


Now im working abit from memory, but when the databse is build, ini=
tdb is using UNICODE as encoding, "C" for locale, so it should be=
independant of the locale settings.


Best Regards
Kim Mortensen

Re: problem connecting via ODBC when unicode (now in correctforum.).

On 01/21/2011 12:35 PM, Kim Mortensen wrote:
> Adrian,
>
> Tje letters you are typing are from Danish, but any will do.
>
> Now im working abit from memory, but when the databse is build, initdb
> is using UNICODE as encoding, "C" for locale, so it should be
> independant of the locale settings.

I believe the problem, as Hiroshi Saito pointed out, is that the client
encoding is being set on Windows before the connection is attempted and
that it is picking the wrong one. Per his post pgAdmin and JDBC are
probably just starting with UTF8 and that is why they succeed. At this
point I don't know how to get around that.

>
> Best Regards
> Kim Mortensen


--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

--20cf3054a5b33edc95049a61efc6
Content-Type: text/plain; charset=ISO-8859-1

But the connector is configures individually and I specify the encoding by
the configuration string sat in the JDBC driver. this string sets the
encoding, but apparently it does not work.

BEst Regards
Kim Mortesnen

2011/1/21 Adrian Klaver

> On 01/21/2011 12:35 PM, Kim Mortensen wrote:
>
>> Adrian,
>>
>> Tje letters you are typing are from Danish, but any will do.
>>
>> Now im working abit from memory, but when the databse is build, initdb
>> is using UNICODE as encoding, "C" for locale, so it should be
>> independant of the locale settings.
>>
>
> I believe the problem, as Hiroshi Saito pointed out, is that the client
> encoding is being set on Windows before the connection is attempted and that
> it is picking the wrong one. Per his post pgAdmin and JDBC are probably just
> starting with UTF8 and that is why they succeed. At this point I don't know
> how to get around that.
>
>
>> Best Regards
>> Kim Mortensen
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>

--20cf3054a5b33edc95049a61efc6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

But the connector is configures individually and I specify the encoding by =
the configuration string sat in the JDBC driver. this string sets the encod=
ing, but apparently it does not work.

BEst Regards
Kim Mortesnen<=
br>

Re: problem connecting via ODBC when unicode (now in correctforum.).

On 01/21/2011 01:28 PM, Kim Mortensen wrote:
> But the connector is configures individually and I specify the encoding
> by the configuration string sat in the JDBC driver. this string sets the
> encoding, but apparently it does not work.
>

It is a chicken and egg problem. The problem is that to set the encoding
you have to connect but to connect the client needs to be using the
correct encoding. In Windows, it seems the ODBC driver is sending the
connection string in the wrong encoding and you never actually connect.


--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

Adrian Klaver writes:
> I believe the problem, as Hiroshi Saito pointed out, is that the client
> encoding is being set on Windows before the connection is attempted and
> that it is picking the wrong one. Per his post pgAdmin and JDBC are
> probably just starting with UTF8 and that is why they succeed. At this
> point I don't know how to get around that.

AFAIK the only way to get the mentioned error message (about "no
pg_hba.conf entry") from this kind of problem is if the DBA is listing
user names directly in pg_hba.conf. Since there is no encoding
conversion active this early in backend startup, non-ASCII user names can
only work if the encoding used in pg_hba.conf matches what is arriving
from the client.

Possible solution: make *two* (or more) entries in pg_hba.conf, one in
each encoding that clients might be using. This is probably
unmaintainable though, particularly when using Windows editors which are
almost certainly going to think they know more than you do about
encodings.

Better idea: refrain from mentioning specific usernames in pg_hba.conf.
Usually, anything you might want to do that way can be done better in
other ways. For instance, make use of the CONNECT privilege if you're
running a release new enough to have it.

By and large, though, non-ASCII user names are going to be problematic
anyway --- even if you got past the pg_hba.conf check, I think you might
just get a "no such user" failure when the client sends the name in an
encoding that doesn't match what's in the system catalog. This is an
area that's known to need work.

regards, tom lane

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: problem connecting via ODBC when unicode (now in correct forum.).

On Friday 21 January 2011 2:17:00 pm Tom Lane wrote:
> Adrian Klaver writes:
> > I believe the problem, as Hiroshi Saito pointed out, is that the client
> > encoding is being set on Windows before the connection is attempted and
> > that it is picking the wrong one. Per his post pgAdmin and JDBC are
> > probably just starting with UTF8 and that is why they succeed. At this
> > point I don't know how to get around that.
>
> AFAIK the only way to get the mentioned error message (about "no
> pg_hba.conf entry") from this kind of problem is if the DBA is listing
> user names directly in pg_hba.conf. Since there is no encoding
> conversion active this early in backend startup, non-ASCII user names can
> only work if the encoding used in pg_hba.conf matches what is arriving
> from the client.

In the message(s) that went private was the pg_hba.conf. It did have some
specific usernames.

>
> Possible solution: make *two* (or more) entries in pg_hba.conf, one in
> each encoding that clients might be using. This is probably
> unmaintainable though, particularly when using Windows editors which are
> almost certainly going to think they know more than you do about
> encodings.
>
> Better idea: refrain from mentioning specific usernames in pg_hba.conf.
> Usually, anything you might want to do that way can be done better in
> other ways. For instance, make use of the CONNECT privilege if you're
> running a release new enough to have it.

When I tested I used 'all' for users to get around the above problem. It got me
past the no pg_hba.conf entry problem.

>
> By and large, though, non-ASCII user names are going to be problematic
> anyway --- even if you got past the pg_hba.conf check, I think you might
> just get a "no such user" failure when the client sends the name in an
> encoding that doesn't match what's in the system catalog. This is an
> area that's known to need work.

What I saw, for the record, in the Windows error message was:
FATAL:password authentication failed for user "?"

>
> regards, tom lane



--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc